250123 资费备份

WITH base_data AS (
SELECT
ps.personnel_name AS 姓名,
ps.employee_id AS 员工编号,
ps.date::date AS 出差日期,
CASE
WHEN ps.subsidy_state = ‘1’ THEN
CASE
WHEN ps.is_hotel = ‘1’ AND ps.is_bicycle = ‘2’ THEN ph.in_hotel
WHEN ps.is_hotel = ‘2’ AND ps.is_bicycle = ‘2’ THEN ph.not_in_hotel
WHEN ps.is_hotel = ‘2’ AND ps.is_bicycle = ‘1’ THEN ph.not_in_hotel
WHEN ps.is_hotel = ‘1’ AND ps.is_bicycle = ‘1’ THEN ph.single_person
ELSE NULL
END
ELSE NULL
END::numeric AS 每日补助
FROM as_personnel_status ps
LEFT JOIN as_person_hotel ph
ON ps.position_province = ph.province
AND ps.position_city = ph.city
WHERE ps.date::date BETWEEN #{startTime} AND #{endTime}
AND ps.subsidy_state = ‘1’ – 仅包含补助状态已通过的记录
), ordered_data AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
LAG(出差日期) OVER (PARTITION BY 员工编号 ORDER BY 出差日期) AS prev_date,
LAG(每日补助) OVER (PARTITION BY 员工编号 ORDER BY 出差日期) AS prev_subsidy
FROM base_data
), grouped_data AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
CASE
WHEN prev_date IS NULL
OR 出差日期 <> prev_date + INTERVAL ‘1 day’
OR 每日补助 <> prev_subsidy THEN 1
ELSE 0
END AS is_new_group
FROM ordered_data
), cumulative_groups AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
SUM(is_new_group) OVER (PARTITION BY 员工编号 ORDER BY 出差日期 ROWS UNBOUNDED PRECEDING) AS group_num
FROM grouped_data
), grouped_by_consecutive AS (
SELECT
姓名,
员工编号,
MIN(出差日期) AS 起始日期,
MAX(出差日期) AS 结束日期,
((MAX(出差日期) - MIN(出差日期)) + 1)::int AS 天数,
MAX(每日补助) AS 区间补助标准
FROM cumulative_groups
GROUP BY 姓名, 员工编号, group_num
), detail_per_group AS (
SELECT
姓名,
员工编号,
CASE
WHEN 起始日期 = 结束日期 THEN TO_CHAR(起始日期, ‘MM.DD’)
ELSE TO_CHAR(起始日期, ‘MM.DD’) || ‘-‘ || TO_CHAR(结束日期, ‘MM.DD’)
END AS 日期区间,
天数::text AS 出差天数段,
CASE
WHEN COALESCE(区间补助标准, 0) < 100 THEN 区间补助标准
ELSE COALESCE(区间补助标准, 0)
END::numeric AS 区间补助标准,
CASE
WHEN COALESCE(区间补助标准, 0) < 100 THEN 区间补助标准
ELSE COALESCE(区间补助标准, 0) * 天数
END AS 区间补助小计,
起始日期 – 添加起始日期用于排序
FROM grouped_by_consecutive
), final_result AS (
SELECT
姓名,
员工编号,
STRING_AGG(日期区间, ‘/‘ ORDER BY 起始日期) AS 日期,
STRING_AGG(出差天数段, ‘/‘ ORDER BY 起始日期) AS 出差天数,
SUM((出差天数段)::int) AS 总天数,
STRING_AGG(区间补助标准::text, ‘/‘ ORDER BY 起始日期) AS 补助标准,
SUM(区间补助小计) AS 补助总金额
FROM detail_per_group
GROUP BY 姓名, 员工编号
)
SELECT
姓名,
员工编号,
日期,
出差天数,
总天数,
补助标准,
补助总金额
FROM final_result
ORDER BY 员工编号

WITH base_data AS (
SELECT
ps.personnel_name AS 姓名,
ps.employee_id AS 员工编号,
ps.date::date AS 出差日期,
CASE
WHEN ps.subsidy_state = ‘1’ THEN
CASE
WHEN ps.is_hotel = ‘1’ AND ps.is_bicycle = ‘2’ THEN ph.in_hotel
WHEN ps.is_hotel = ‘2’ AND ps.is_bicycle = ‘2’ THEN ph.not_in_hotel
WHEN ps.is_hotel = ‘2’ AND ps.is_bicycle = ‘1’ THEN ph.not_in_hotel
WHEN ps.is_hotel = ‘1’ AND ps.is_bicycle = ‘1’ THEN ph.single_person
ELSE NULL
END
ELSE NULL
END::numeric AS 每日补助
FROM as_personnel_status ps
– 关键：使用 LATERAL 子查询，按优先级匹配 as_person_hotel
LEFT JOIN LATERAL (
SELECT *
FROM as_person_hotel h
WHERE
h.province = ps.position_province
AND (
– 情形1：省=市时，用 ps.position_area 作为 city
(ps.position_province = ps.position_city AND h.city = ps.position_area)
– 情形2：正常情况，用 position_city
OR (ps.position_province <> ps.position_city AND h.city = ps.position_city)
– 情形3：再找不到，就退回省份当作 city
OR (h.city = ps.position_province)
)
ORDER BY
CASE
WHEN ps.position_province = ps.position_city AND h.city = ps.position_area THEN 1
WHEN ps.position_province <> ps.position_city AND h.city = ps.position_city THEN 2
ELSE 3
END
LIMIT 1
) ph ON true
WHERE ps.date::date BETWEEN #{startTime} AND #{endTime}
AND ps.subsidy_state = ‘1’ – 仅包含补助状态已通过的记录
),
ordered_data AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
LAG(出差日期) OVER (PARTITION BY 员工编号 ORDER BY 出差日期) AS prev_date,
LAG(每日补助) OVER (PARTITION BY 员工编号 ORDER BY 出差日期) AS prev_subsidy
FROM base_data
),
grouped_data AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
CASE
WHEN prev_date IS NULL
OR 出差日期 <> prev_date + INTERVAL ‘1 day’
OR 每日补助 <> prev_subsidy
THEN 1
ELSE 0
END AS is_new_group
FROM ordered_data
),
cumulative_groups AS (
SELECT
姓名,
员工编号,
出差日期,
每日补助,
SUM(is_new_group) OVER (PARTITION BY 员工编号 ORDER BY 出差日期 ROWS UNBOUNDED PRECEDING) AS group_num
FROM grouped_data
),
grouped_by_consecutive AS (
SELECT
姓名,
员工编号,
MIN(出差日期) AS 起始日期,
MAX(出差日期) AS 结束日期,
((MAX(出差日期) - MIN(出差日期)) + 1)::int AS 天数,
– 这里取 MAX(每日补助)，因为同一个 group_num 下每日补助理论上相同
MAX(每日补助) AS 区间补助标准
FROM cumulative_groups
GROUP BY 姓名, 员工编号, group_num
),
detail_per_group AS (
SELECT
姓名,
员工编号,
CASE
WHEN 起始日期 = 结束日期 THEN TO_CHAR(起始日期, ‘MM.DD’)
ELSE TO_CHAR(起始日期, ‘MM.DD’) || ‘-‘ || TO_CHAR(结束日期, ‘MM.DD’)
END AS 日期区间,
天数::text AS 出差天数段,
CASE
WHEN COALESCE(区间补助标准, 0) < 100 THEN 区间补助标准
ELSE COALESCE(区间补助标准, 0)
END::numeric AS 区间补助标准,
CASE
WHEN COALESCE(区间补助标准, 0) < 100 THEN 区间补助标准
ELSE COALESCE(区间补助标准, 0) * 天数
END AS 区间补助小计,
起始日期 – 用于后面在 STRING_AGG 中排序
FROM grouped_by_consecutive
),
final_result AS (
SELECT
姓名,
员工编号,
STRING_AGG(日期区间, ‘/‘ ORDER BY 起始日期) AS 日期,
STRING_AGG(出差天数段, ‘/‘ ORDER BY 起始日期) AS 出差天数,
SUM( (出差天数段)::int ) AS 总天数,
STRING_AGG(区间补助标准::text, ‘/‘ ORDER BY 起始日期) AS 补助标准,
SUM(区间补助小计) AS 补助总金额
FROM detail_per_group
GROUP BY 姓名, 员工编号
)
SELECT
姓名,
员工编号,
日期,
出差天数,
总天数,
补助标准,
补助总金额
FROM final_result
ORDER BY 员工编号;

导出人员人员状态-资费导出。导出的时候会带入当前登录用户的id
LoginUser user = tokenService.getLoginUser(ServletUtils.getRequest());
Long roleId = sysUserService.selectUserRoleByUserId(user.getUser().getUserId());
if (roleId != 1){
asPersonnelStatus.setPersonnelId(user.getUser().getUserId());
}
然后从下面表：
– auto-generated definition
create table sys_user_project
(
id bigint default nextval(‘sys_user_project_seq’::regclass) not null
primary key,
project_id bigint,
user_id bigint,
is_created char default ‘0’::bpchar
);

找到当前对应的项目，也就是导出的时候这个人只能导出他关联项目信息的数据